﻿//=================================================================
// Copyright © 2006 Microsoft Corporation. All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//==================================================================
// Project    : Titan Framework
// Description: 
// 数据库访问辅助类
// Change History:
//  When        Who         Why
//----------------------------------------------------------------
//  2011/05/09  Julian.Hu     Created. 
//
// © 2010-2011 Microsoft
// All Rights Reserved
//----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Collections;
using Titan.Common;
using Titan.Logging;
using Titan.Excepting;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Titan.Data
{
    public sealed class SqlHelper
    {
        #region Fields
        /// <summary>
        ///  the mapping of DbType and System.Type
        /// </summary>
        private static String[,] DBTypeConversionKey = new String[,] 
            {
             {"Binary", "System.Byte[]"},
             {"Byte", "System.Byte"},
             {"Boolean", "System.Boolean"},
             {"DateTime", "System.DateTime"},
             {"Decimal", "System.Decimal"},
             {"Double", "System.Double"},
             {"Guid", "System.Guid"},
             {"Int16", "System.Int16"},
             {"Int32", "System.Int32"},
             {"Int64", "System.Int64"},
             {"Object", "System.Object"},
             {"SByte", "System.SByte"},
             {"Single", "System.Single"},
             {"String", "System.String"},
             {"UInt16", "System.UInt16"},
             {"UInt32", "System.UInt32"},
             {"UInt64", "System.UInt64"},
             {"Xml", "System.Xml"}
            };
        private string _dbName = "";
        private ILogger _logger = new LoggerFactory().GetLogger(typeof(SqlHelper));

        private readonly string EXCEPTION_POLICY = "DAL policy";

        private Database _db = null;
        #endregion

        #region Properties
        public string DbName
        {
            get { return _dbName; }
            set { _dbName = value; }
        }

        public Database CurrentDataBase
        {
            get
            {
                if (this._db == null)
                {
                    this._db = DatabaseFactory.CreateDatabase();
                }

                return this._db;
            }
        }
        #endregion

        #region Methods

        /// <summary>
        /// 
        /// </summary>
        /// <param name="field"></param>
        /// <param name="objInfo"></param>
        /// <returns></returns>
        private bool IsNull(PropertyInfo property, object objInfo)
        {
            object fValue = property.GetValue(objInfo, null);

            if (fValue == null) return true;

            switch (property.PropertyType.FullName)
            {
                case "System.String": if (fValue.ToString() == string.Empty) return true; break;
                case "System.Guid": if ((Guid)fValue == Guid.Empty) return true; break;
                case "System.DateTime": if ((DateTime)fValue == DateTime.MinValue) return true; break;
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                    if ((int)fValue == -1) return true; break;
                default: return true;
            }

            return false;
        }

        private string Param2Str(SqlParameter[] sqlParams)
        {
            string result = "";
            if (sqlParams != null)
            {
                foreach (SqlParameter sqlParam in sqlParams)
                {
                    if (result == "")
                    {
                        result = sqlParam.ParameterName + "=" + sqlParam.Value.ToString();
                    }
                    else
                    {
                        result = result + ";" + sqlParam.ParameterName + "=" + sqlParam.Value.ToString();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// fill he data object from datareader
        /// </summary>
        /// <param name="theReader">data reader</param>
        /// <param name="objInfo">the data object</param>
        /// <returns></returns>
        public T GetFromReader<T>(IDataReader theReader)
        {
            Type objType = typeof(T);

            object objInfo = Activator.CreateInstance(objType);

            PropertyInfo[] properties = objType.GetProperties();

            if (theReader.Read())
            {
                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        if (theReader[property.Name] == null || theReader[property.Name] == DBNull.Value) continue;

                        property.SetValue(objInfo, theReader[property.Name].ToString(), null);
                    }
                    catch { }
                }

            }
            else return default(T);


            return (T)objInfo;
        }

        /// <summary>
        /// Fill the data object list from datareader
        /// </summary>
        /// <typeparam name="T">Object Type</typeparam>
        /// <param name="theReader">data reader</param>
        /// <returns>data object list</returns>
        public List<T> GetListFromReader<T>(IDataReader theReader)
        {
            List<T> objList = new List<T>();
            object objInfo = null;

            Type objType = typeof(T);
            PropertyInfo[] properties = objType.GetProperties();

            while (theReader.Read())
            {
                objInfo = Activator.CreateInstance(objType);

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        if (theReader[property.Name] == null || theReader[property.Name] == DBNull.Value) continue;

                        property.SetValue(objInfo, theReader[property.Name].ToString(), null);
                    }
                    catch { }
                }

                objList.Add((T)objInfo);
            }


            return objList;
        }

        /// <summary>
        /// Fill the data object list from datareader
        /// </summary>
        /// <typeparam name="T">Object Type</typeparam>
        /// <param name="theReader">data reader</param>
        /// <returns>data object list</returns>
        public List<T> GetListFromDataTable<T>(DataTable dt)
        {
            List<T> objList = new List<T>();
            object objInfo = null;

            Type objType = typeof(T);
            PropertyInfo[] properties = objType.GetProperties();

            foreach (DataRow dr in dt.Rows)
            {
                objInfo = Activator.CreateInstance(objType);

                foreach (PropertyInfo property in properties)
                {
                    try
                    {
                        if (dr[property.Name] == null || dr[property.Name] == DBNull.Value) continue;

                        property.SetValue(objInfo, dr[property.Name].ToString(), null);
                    }
                    catch { }
                }

                objList.Add((T)objInfo);
            }

            return objList;
        }

        public SqlHelper()
        {
            _logger.Debug("start create database connection");
            _db = DatabaseFactory.CreateDatabase();
        }

        public SqlHelper(string dbName)
        {
            _logger.Debug("start create database connection");
            _db = DatabaseFactory.CreateDatabase(dbName);
            _dbName = dbName;
        }

        public Database GetDataBase(string dbName)
        {
            return DatabaseFactory.CreateDatabase(dbName);
        }

        /// <summary>
        /// convert to BType from System.Type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>

        /// <summary>
        /// Insert a data object
        /// </summary>
        /// <param name="objInfo">the data object</param>
        public void Insert(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }

            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

            string sqlStr = "Insert Into {0} ({1}) Values ({2})";

            string fieldNames = "";

            string fieldValues = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {


                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) continue;

                fieldNames += property.Name + ",";

                fieldValues += "@" + property.Name + ",";
            }


            if (fieldNames != "") fieldNames = fieldNames.Trim(',');
            if (fieldValues != "") fieldValues = fieldValues.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldNames, fieldValues);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) continue;

                if (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objInfo, null) == Guid.Empty)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }
                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
            }
            _db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Insert a data object
        /// </summary>
        /// <param name="db">Database Name</param>
        /// <param name="objInfo">the data object</param>
        public void Insert(string dbName, object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }

            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

            string sqlStr = "Insert Into {0} ({1}) Values ({2})";

            string fieldNames = "";

            string fieldValues = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {

                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) continue;

                fieldNames += property.Name + ",";

                fieldValues += "@" + property.Name + ",";
            }

            if (fieldNames != "") fieldNames = fieldNames.Trim(',');
            if (fieldValues != "") fieldValues = fieldValues.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldNames, fieldValues);


            Database db = GetDataBase(dbName);
            DbCommand dbCommand = db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) continue;

                if (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objInfo, null) == Guid.Empty)
                {
                    db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }

                db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
            }

            db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Insert an array of data object
        /// </summary>
        /// <param name="objInfos"> the array of object</param>
        public void Insert(object[] objInfos)
        {
            foreach (object objInfo in objInfos)
            {
                Insert(objInfo);
            }
        }

        /// <summary>
        /// update a data object
        /// </summary>
        /// <param name="objInfo">the data object</param>
        public void Update(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }

            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

            string sqlStr = "Update {0} Set {1} Where {2}";

            string fieldSets = "";

            string fieldWheres = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {
                //if (field.FieldType == typeof(DateTime) && (DateTime)field.GetValue(objInfo) == DateTime.MinValue) continue;

                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);


                if (!IsNull(property, objInfo))
                {
                    fieldWheres += property.Name + " = @" + property.Name + " And ";
                }

                fieldSets += property.Name + "=@" + property.Name + ",";

            }

            if (fieldWheres != "")
            {
                fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);
            }
            else
            {
                sqlStr = sqlStr.Replace("Where", "");
            };

            if (fieldSets != "") fieldSets = fieldSets.Trim(',');


            sqlStr = string.Format(sqlStr, tbName, fieldSets, fieldWheres);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                //GUID EMPTY 或者 DATETIME MINVALUE 表示数据库为空
                if ((property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) || (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objInfo, null) == Guid.Empty))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }

                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
            }

            _db.ExecuteNonQuery(dbCommand);


        }

        /// <summary>
        /// 检查当前实体是否被引用
        /// </summary>
        /// <param name="objInfo"></param>
        /// <returns></returns>
        public bool CheckReference(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }
            bool isReferenced = false;
            Type objType = objInfo.GetType();
            ReferenceTableName referenceTable = null;
            string[] referenceTableName = null;

            object[] referenceTableNameAtts = objType.GetCustomAttributes(typeof(ReferenceTableName), false);
            PropertyInfo[] properties = objType.GetProperties();

            if (referenceTableNameAtts != null && referenceTableNameAtts.Length > 0)
            {
                referenceTable = (ReferenceTableName)referenceTableNameAtts[0];
                referenceTableName = referenceTable.Name.Split(new char[] { ',' });

                if (referenceTableName != null && referenceTableName.Length > 0)
                {
                    for (int index = 0; index < referenceTableName.Length; index++)
                    {

                        isReferenced = this.CheckReference(objInfo, properties, referenceTableName[index]);
                        if (isReferenced)
                        {
                            break;
                        }
                    }
                }
            }


            return isReferenced;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objInfo"></param>
        /// <param name="fields"></param>
        /// <param name="referenceTable"></param>
        /// <returns></returns>
        private bool CheckReference(object objInfo, PropertyInfo[] properties, string referenceTableName)
        {
            string sqlStr = "Select {1} From {0} Where {2}";
            string selectFields = "";
            string fieldWheres = "";

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    selectFields += property.Name + ",";
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }

            }

            if (selectFields != "") selectFields = selectFields.Trim(',');
            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            if (referenceTableName.IndexOf(".dbo.") >= 0 && this._dbName != string.Empty)
                referenceTableName = "[" + this._dbName + "]" + ".[dbo]." + "[" + referenceTableName + "]";
            sqlStr = string.Format(sqlStr, referenceTableName, selectFields, fieldWheres);


            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                }
            }

            return this._db.ExecuteScalar(dbCommand) != null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objInfo"></param>
        /// <returns></returns>
        public bool CheckUnique(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }
            string sqlStr = "Select {1} From {0} Where {2}";
            StringBuilder sbPKFilter = new StringBuilder();
            StringBuilder sbUIFilter = new StringBuilder();
            StringBuilder sbFileds = new StringBuilder();
            StringBuilder sbParents = new StringBuilder();
            StringBuilder sbDisableFilters = new StringBuilder();
            StringBuilder filter = new StringBuilder();
            string tbName = string.Empty;

            Type objType = objInfo.GetType();
            PropertyInfo[] properties = objType.GetProperties();

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);
            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";


            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                object[] uniqueAtts = property.GetCustomAttributes(typeof(UniqueCheck), false);
                object[] parentAtts = property.GetCustomAttributes(typeof(ParentFiled), false);
                object[] disableFilterAtts = property.GetCustomAttributes(typeof(DisableFilter), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    sbFileds.AppendFormat("{0},", property.Name);
                    sbPKFilter.AppendFormat("{0} <> @{0} AND ", property.Name);
                }

                if (uniqueAtts != null && uniqueAtts.Length > 0)
                {
                    sbUIFilter.AppendFormat("{0} = @{0} OR ", property.Name);
                }

                if (parentAtts != null && parentAtts.Length > 0)
                {
                    sbParents.AppendFormat("{0} = @{0} AND ", property.Name);
                }

                if (disableFilterAtts != null && disableFilterAtts.Length > 0)
                {
                    sbDisableFilters.AppendFormat("{0} = @{0} AND ", property.Name);
                }

            }

            if (sbPKFilter.Length > 0)
            {
                filter.AppendFormat("{0} AND ", sbPKFilter.ToString().TrimEnd("AND ".ToCharArray()));
            }

            if (sbUIFilter.Length > 0)
            {
                filter.AppendFormat("{0} AND ", sbUIFilter.ToString().TrimEnd("OR ".ToCharArray()));
            }

            if (sbDisableFilters.Length > 0)
            {
                filter.AppendFormat("{0} AND ", sbDisableFilters.ToString().TrimEnd("AND ".ToCharArray()));
            }

            if (sbParents.Length > 0)
            {
                filter.AppendFormat("{0} AND ", sbParents.ToString().TrimEnd("AND ".ToCharArray()));
            }

            sqlStr = string.Format(sqlStr, tbName, sbFileds.ToString().TrimEnd(",".ToCharArray()), filter.ToString().TrimEnd("AND ".ToCharArray()));


            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                object[] uniqueAtts = property.GetCustomAttributes(typeof(UniqueCheck), false);
                object[] parentAtts = property.GetCustomAttributes(typeof(ParentFiled), false);
                object[] disableFilterAtts = property.GetCustomAttributes(typeof(DisableFilter), false);

                if ((pkAtts != null && pkAtts.Length > 0) ||
                    (uniqueAtts != null && uniqueAtts.Length > 0) ||
                    (parentAtts != null && parentAtts.Length > 0))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                }

                if (disableFilterAtts != null && disableFilterAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), 0);
                }
            }

            return this._db.ExecuteScalar(dbCommand) == null;

        }

        /// <summary>
        /// 逻辑删除数据
        /// </summary>
        /// <param name="objInfo">需要被删除的数据信息</param>
        public void Disable(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }
            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";


            string sqlStr = "Update {0} Set {1} Where {2}";

            string fieldSets = "";

            string fieldWheres = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {
                if (property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) continue;

                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                object[] disableAtts = property.GetCustomAttributes(typeof(DisableFilter), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }

                if (disableAtts != null && disableAtts.Length > 0)
                {
                    fieldSets += property.Name + "=@" + property.Name + ",";
                }


            }

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            if (fieldSets != "") fieldSets = fieldSets.Trim(',');


            sqlStr = string.Format(sqlStr, tbName, fieldSets, fieldWheres);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                object[] disableAtts = property.GetCustomAttributes(typeof(DisableFilter), false);

                if ((pkAtts != null && pkAtts.Length > 0)
                    || (disableAtts != null && disableAtts.Length > 0))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                }

            }

            _db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// Update an array data object
        /// </summary>
        /// <param name="objInfos"> the array of object</param>
        public void Update(object[] objInfos)
        {
            foreach (object objInfo in objInfos)
            {
                Update(objInfo);
            }
        }

        /// <summary>
        /// insert or update a data object 
        /// </summary>
        /// <param name="objInfo">the data object</param>
        public void InsertOrUpdate(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }
            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";


            string sqlStr = "IF NOT EXISTS(SELECT * FROM {0} WHERE {2}) INSERT INTO {0} ({3}) VALUES ({4}) ELSE UPDATE {0} SET {1} WHERE {2} ";

            string fieldSets = "";

            string fieldWheres = "";

            string fieldNames = "";

            string fieldValues = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {
                //if (field.FieldType == typeof(DateTime) && (DateTime)field.GetValue(objInfo) == DateTime.MinValue) continue;

                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += "[" + property.Name + "]=@" + property.Name + " And ";
                }

                object[] pkAttsIdentity = property.GetCustomAttributes(typeof(Identity), false);

                if (pkAttsIdentity == null || pkAttsIdentity.Length == 0)
                {
                    fieldNames += "[" + property.Name + "],";

                    fieldValues += "@" + property.Name + ",";

                    fieldSets += "[" + property.Name + "]=@" + property.Name + ",";
                }
            }

            if (fieldNames != "") fieldNames = fieldNames.Trim(',');

            if (fieldValues != "") fieldValues = fieldValues.Trim(',');

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            if (fieldSets != "") fieldSets = fieldSets.Trim(',');

            sqlStr = string.Format(sqlStr, tbName, fieldSets, fieldWheres, fieldNames, fieldValues);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                //if (field.FieldType == typeof(DateTime) && (DateTime)field.GetValue(objInfo) == DateTime.MinValue) continue;

                //GUID EMPTY 或者 DATETIME MINVALUE 表示数据库为空
                if ((property.PropertyType == typeof(DateTime) && (DateTime)property.GetValue(objInfo, null) == DateTime.MinValue) || (property.PropertyType == typeof(Guid) && (Guid)property.GetValue(objInfo, null) == Guid.Empty))
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), null);
                    continue;
                }

                _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
            }

            _db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// insert or update an array of data object 
        /// </summary>
        /// <param name="objInfos">the array of object</param>
        public void InsertOrUpdate(object[] objInfos)
        {
            foreach (object objInfo in objInfos)
            {
                InsertOrUpdate(objInfo);
            }
        }

        /// <summary>
        /// delete the data object
        /// </summary>
        /// <param name="objInfo">the data object</param>
        public void Delete(object objInfo)
        {
            if ((objInfo is DataEntityBase) && (objInfo != null))
            {
                _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
            }
            Type objType = objInfo.GetType();

            string tbName = objType.Name;

            object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

            if (tbNameAtts != null && tbNameAtts.Length > 0)
            {
                TableName tableName = (TableName)tbNameAtts[0];

                tbName = tableName.Name;
            }

            if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

            string sqlStr = "Delete From {0} Where {1}";

            string fieldWheres = "";

            PropertyInfo[] properties = objType.GetProperties();

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    fieldWheres += property.Name + "=@" + property.Name + " And ";
                }

            }

            if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

            sqlStr = string.Format(sqlStr, tbName, fieldWheres);

            DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

            foreach (PropertyInfo property in properties)
            {
                object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                if (pkAtts != null && pkAtts.Length > 0)
                {
                    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                }
            }

            _db.ExecuteNonQuery(dbCommand);

        }

        public List<object> QueryObjects(object objInfo)
        {
            List<object> result = null;
            DataSet resultDB = null;
            try
            {
                if ((objInfo is DataEntityBase) && (objInfo != null))
                {
                    _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
                }
                Type objType = objInfo.GetType();

                string tbName = objType.Name;

                object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

                if (tbNameAtts != null && tbNameAtts.Length > 0)
                {
                    TableName tableName = (TableName)tbNameAtts[0];

                    tbName = tableName.Name;
                }

                if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                    tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

                string sqlStr = "Select {1} From {0} Where {2}";

                string fieldWheres = "";

                string selectFields = "";

                PropertyInfo[] properties = objType.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    selectFields += property.Name + ",";

                    //object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                    //if ((pkAtts == null || pkAtts.Length <= 0) && !IsNull(property, objInfo))
                    //{
                    //    if (property.GetValue(objInfo, null).ToString().IndexOf("%") >= 0)
                    //    {
                    //        fieldWheres += property.Name + " like @" + property.Name + " And ";

                    //    }
                    //    else
                    //        fieldWheres += property.Name + " = @" + property.Name + " And ";
                    //}
                    //修改该功能旨在能够根据实体类中的所有字段查询数据库。
                    if (!IsNull(property, objInfo))
                    {
                        if (property.GetValue(objInfo, null).ToString().IndexOf("%") >= 0)
                        {
                            fieldWheres += property.Name + " like @" + property.Name + " And ";

                        }
                        else
                            fieldWheres += property.Name + " = @" + property.Name + " And ";
                    }

                }

                if (selectFields != "") selectFields = selectFields.Trim(',');
                if (fieldWheres != "")
                {
                    fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);
                }
                else
                {
                    sqlStr = sqlStr.Replace("Where", "");
                }

                sqlStr = string.Format(sqlStr, tbName, selectFields, fieldWheres);


                DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

                foreach (PropertyInfo property in properties)
                {
                    //object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                    //if ((pkAtts == null || pkAtts.Length <= 0) && !IsNull(property, objInfo))
                    //{
                    //    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo,null));
                    //}
                    if (!IsNull(property, objInfo))
                    {
                        _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                    }
                }

                result = new List<object>();

                resultDB = _db.ExecuteDataSet(dbCommand);

                foreach (DataRow dr in resultDB.Tables[0].Rows)
                {
                    object resultItem = Activator.CreateInstance(objType);
                    foreach (PropertyInfo property in properties)
                    {
                        property.SetValue(resultItem, dr[property.Name].ToString(), null);
                    }
                    result.Add(resultItem);
                }
            }
            catch (DataException er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (ReflectionTypeLoadException er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="objInfo"></param>
        /// <returns></returns>
        public DataSet Query(object objInfo)
        {
            DataSet result = null;
            try
            {
                if ((objInfo is DataEntityBase) && (objInfo != null))
                {
                    _logger.Debug(((DataEntityBase)objInfo).getLoggingInfo());
                }
                Type objType = objInfo.GetType();

                string tbName = objType.Name;

                object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

                if (tbNameAtts != null && tbNameAtts.Length > 0)
                {
                    TableName tableName = (TableName)tbNameAtts[0];

                    tbName = tableName.Name;
                }

                if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                    tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

                string sqlStr = "Select {1} From {0} Where {2}";

                string fieldWheres = "";

                string selectFields = "";

                PropertyInfo[] properties = objType.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    selectFields += property.Name + ",";

                    //object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                    //if ((pkAtts == null || pkAtts.Length <= 0) && !IsNull(property, objInfo))
                    //{
                    //    if (property.GetValue(objInfo, null).ToString().IndexOf("%") >= 0)
                    //    {
                    //        fieldWheres += property.Name + " like @" + property.Name + " And ";

                    //    }
                    //    else
                    //        fieldWheres += property.Name + " = @" + property.Name + " And ";
                    //}
                    //修改该功能旨在能够根据实体类中的所有字段查询数据库。
                    if (!IsNull(property, objInfo))
                    {
                        if (property.GetValue(objInfo, null).ToString().IndexOf("%") >= 0)
                        {
                            fieldWheres += property.Name + " like @" + property.Name + " And ";

                        }
                        else
                            fieldWheres += property.Name + " = @" + property.Name + " And ";
                    }

                }

                if (selectFields != "") selectFields = selectFields.Trim(',');
                if (fieldWheres != "")
                {
                    fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);
                }
                else
                {
                    sqlStr = sqlStr.Replace("Where", "");
                }

                sqlStr = string.Format(sqlStr, tbName, selectFields, fieldWheres);


                DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

                foreach (PropertyInfo property in properties)
                {
                    //object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                    //if ((pkAtts == null || pkAtts.Length <= 0) && !IsNull(property, objInfo))
                    //{
                    //    _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo,null));
                    //}
                    if (!IsNull(property, objInfo))
                    {
                        _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), property.GetValue(objInfo, null));
                    }
                }

                result = _db.ExecuteDataSet(dbCommand);
            }
            catch (DataException er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (ReflectionTypeLoadException er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }

        /// <summary>
        ///  get the data object by primary key and object type
        /// </summary>
        /// <param name="primaryID">primary key</param>
        /// <param name="objType">object type</param>
        /// <returns>the data object</returns>
        public T Get<T>(object primaryID)
        {
            T result = default(T);
            try
            {
                Type objType = typeof(T);

                string tbName = objType.Name;

                object[] tbNameAtts = objType.GetCustomAttributes(typeof(TableName), false);

                if (tbNameAtts != null && tbNameAtts.Length > 0)
                {
                    TableName tableName = (TableName)tbNameAtts[0];

                    tbName = tableName.Name;
                }

                if (tbName.IndexOf(".dbo.") < 0 && this._dbName != string.Empty)
                    tbName = "[" + this._dbName + "]" + ".[dbo]." + "[" + tbName + "]";

                string sqlStr = "Select {1} From {0} Where {2}";

                string fieldWheres = "";

                string selectFields = "";

                PropertyInfo[] properties = objType.GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    selectFields += "[" + property.Name + "],";

                    object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);
                    
                    if (pkAtts != null && pkAtts.Length > 0)
                    {
                        fieldWheres += "[" + property.Name + "]=@" + property.Name + " And ";
                    }

                }

                if (selectFields != "") selectFields = selectFields.Trim(',');
                if (fieldWheres != "") fieldWheres = fieldWheres.Substring(0, fieldWheres.Length - 4);

                sqlStr = string.Format(sqlStr, tbName, selectFields, fieldWheres);


                DbCommand dbCommand = _db.GetSqlStringCommand(sqlStr);

                foreach (PropertyInfo property in properties)
                {
                    object[] pkAtts = property.GetCustomAttributes(typeof(PrimaryKey), false);

                    if (pkAtts != null && pkAtts.Length > 0)
                    {
                        _db.AddInParameter(dbCommand, "@" + property.Name, CovertToDBType(property.PropertyType), primaryID);
                    }
                }

                _logger.Debug("start Get<T> sql statement=" + dbCommand.CommandText);

                ;
                using (IDataReader theReader = _db.ExecuteReader(dbCommand))
                {
                    result = GetFromReader<T>(theReader);
                }
                _logger.Debug("finish Get<T>");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteScalarSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteScalarSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DbType CovertToDBType(System.Type type)
        {
            String dbTypeName = String.Empty;
            if (type.FullName.Contains("SPDB.OA.Common.Utility.Enumerator"))
            {
                dbTypeName = "Int32";
            }
            else
            {
                int keyCount = DBTypeConversionKey.GetLength(0);
                for (int i = 0; i < keyCount; i++)
                {
                    if (DBTypeConversionKey[i, 1].Equals(type.FullName))
                    {
                        dbTypeName = DBTypeConversionKey[i, 0];
                        break;
                    }

                }
            }
            if (dbTypeName == String.Empty)
            {
                dbTypeName = "String";
            }
            //dbTypeName = "Variant";
            return (DbType)Enum.Parse(typeof(DbType), dbTypeName);

        }

        /// <summary>
        /// get data from database return scalar
        /// </summary>
        /// <param name="sql">sql statement</param>
        /// <param name="sqlParams">param list</param>
        /// <returns></returns>
        [Obsolete]
        public object ExecuteScalarSQL(string sql, SqlParameter[] sqlParams)
        {
            object result = null;
            try
            {
                DbCommand dbCommand = _db.GetSqlStringCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteScalarSQL sqlstatement=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteScalarSQL sqlstatement=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;
                result = _db.ExecuteScalar(dbCommand);

                _logger.Debug("finish ExecuteScalarSQL");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteScalarSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteScalarSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }

        /// <summary>
        ///  
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public object ExecuteScalarSP(string spName, SqlParameter[] sqlParams)
        {
            object result = null;
            try
            {
                DbCommand dbCommand = _db.GetStoredProcCommand(spName);


                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteScalarSP sp name=" + spName);
                }
                else
                {
                    _logger.Debug("start ExecuteScalarSP sp name=" + spName + "; sql parameters=" + Param2Str(sqlParams));
                }
                ;
                result = _db.ExecuteScalar(dbCommand);
                _logger.Debug("finish ExecuteScalarSP");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteScalarSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteScalarSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public int ExecuteNonQuerySQL(string sql, SqlParameter[] sqlParams)
        {
            int result = 0;
            try
            {
                DbCommand dbCommand = _db.GetSqlStringCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteNonQuerySQL sqlstatement=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteNonQuerySQL sqlstatement=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;

                result = _db.ExecuteNonQuery(dbCommand);

                _logger.Debug("finsh ExecuteNonQuerySQL");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteNonQuerySQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteNonQuerySQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public object ExecuteNonQuerySP(string spName, SqlParameter[] sqlParams)
        {
            object result = null;
            try
            {
                DbCommand dbCommand = _db.GetStoredProcCommand(spName);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }
                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteNonQuerySP sp name=" + spName);
                }
                else
                {
                    _logger.Debug("start ExecuteNonQuerySP sp name=" + spName + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;
                result = _db.ExecuteNonQuery(dbCommand);
                _logger.Debug("finish ExecuteNonQuerySP");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteNonQuerySP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteNonQuerySP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public DataTable ExecuteDataTableSQL(string sql, SqlParameter[] sqlParams)
        {
            DataTable result = null;
            try
            {
                DbCommand dbCommand = _db.GetSqlStringCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }
                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteDataTableSQL sql statememt=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteDataTableSQL sql statememt=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }
                ;
                DataTable dt = _db.ExecuteDataSet(dbCommand).Tables[0];
                _logger.Debug("finish ExecuteDataTableSQL");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteDataTableSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteDataTableSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public DataTable ExecuteDataTableSP(string spName, SqlParameter[] sqlParams)
        {
            DataTable result = null;
            try
            {
                DbCommand dbCommand = _db.GetStoredProcCommand(spName);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                ;
                result = _db.ExecuteDataSet(dbCommand).Tables[0];
                _logger.Debug("finish ExecuteDataTableSP");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteDataTableSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }

            }
            catch (Exception er)
            {
                _logger.Error("ExecuteDataTableSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }

            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public DataSet ExecuteDataSetSQL(string sql, SqlParameter[] sqlParams)
        {
            DataSet result = null;
            try
            {
                DbCommand dbCommand = _db.GetSqlStringCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }
                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteDataSetSQL sp Name=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteDataSetSQL sp Name=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;
                result = _db.ExecuteDataSet(dbCommand);
                _logger.Debug("finish ExecuteDataSetSQL");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteDataSetSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteDataSetSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public DataSet ExecuteDataSetSP(string spName, SqlParameter[] sqlParams)
        {
            DataSet result = null;
            try
            {
                DbCommand dbCommand = _db.GetStoredProcCommand(spName);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }
                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteDataSetSP sp Name=" + spName);
                }
                else
                {
                    _logger.Debug("start ExecuteDataSetSP sp Name=" + spName + "; sql parameters=" + Param2Str(sqlParams));
                }
                ;
                result = _db.ExecuteDataSet(dbCommand);
                _logger.Debug("finish ExecuteDataSetSP");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteDataSetSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteDataSetSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public SqlDataReader ExecuteReaderSQL(string sql, SqlParameter[] sqlParams)
        {
            SqlDataReader result = null;
            try
            {
                DbCommand dbCommand = _db.GetSqlStringCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteReaderSQL sql statememt=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteReaderSQL sql statememt=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;
                result = _db.ExecuteReader(dbCommand) as SqlDataReader;
                _logger.Debug("finish ExecuteReaderSQL");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteReaderSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            catch (Exception er)
            {
                _logger.Error("ExecuteReaderSQL Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        [Obsolete]
        public SqlDataReader ExecuteReaderSP(string sql, SqlParameter[] sqlParams)
        {
            SqlDataReader result = null;
            try
            {
                DbCommand dbCommand = _db.GetStoredProcCommand(sql);

                if (sqlParams != null)
                {
                    foreach (SqlParameter sqlParam in sqlParams)
                        dbCommand.Parameters.Add(sqlParam);
                }

                if (sqlParams != null)
                {
                    _logger.Debug("start ExecuteDataTableSQL sql statememt=" + sql);
                }
                else
                {
                    _logger.Debug("start ExecuteDataTableSQL sql statememt=" + sql + "; sql parameters=" + Param2Str(sqlParams));
                }

                ;
                result = _db.ExecuteReader(dbCommand) as SqlDataReader;
                _logger.Debug("finish ExecuteReaderSP");
            }
            catch (DbException er)
            {
                _logger.Error("ExecuteReaderSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }

            }
            catch (Exception er)
            {
                _logger.Error("ExecuteReaderSP Error, Error Message:" + er.Message);
                bool rethrow = ExceptionHelper.HandleException(er, EXCEPTION_POLICY);
                if (rethrow)
                {
                    throw er;
                }
            }
            return result;
        }


        #endregion
    }
}

